<?php
// CVS: $Id: Pager_Wrapper.php,v 1.20 2005/06/27 10:34:10 quipo Exp $
//
// Pager_Wrapper
// -------------
//
// Ready-to-use wrappers for paging the result of a query,
// when fetching the whole resultset is NOT an option.
// This is a performance- and memory-savvy method
// to use PEAR::Pager with a database.
// With this approach, the network load can be
// consistently smaller than with PEAR::DB_Pager.
//
// The following wrappers are provided: one for each PEAR
// db abstraction layer (DB, MDB and MDB2), one for
// PEAR::DB_DataObject, and one for the PHP Eclipse library
//
//
// SAMPLE USAGE
// ------------
//
// $query = 'SELECT this, that FROM mytable';
// require_once 'Pager_Wrapper.php'; //this file
// $pagerOptions = array(
//     'mode'    => 'Sliding',
//     'delta'   => 2,
//     'perPage' => 15,
// );
// $paged_data = Pager_Wrapper_MDB2($db, $query, $pagerOptions);
// //$paged_data['data'];  //paged data
// //$paged_data['links']; //xhtml links for page navigation
// //$paged_data['page_numbers']; //array('current', 'total');
//

/**
    * Helper method - Rewrite the query into a "SELECT COUNT(*)" query.
    * @param string $sql query
    * @return string rewritten query OR false if the query can't be rewritten
    * @access private
    */
function rewriteCountQuery($sql) {
    
    $queryCount = '';
    
    if (preg_match('/^\s*SELECT\s+\bDISTINCT\b/is', $sql) || preg_match('/\s+GROUP\s+BY\s+/is', $sql)) {
        return false;
    }
    
    if (preg_match("/UNION/", $sql)) {
        /*
        $queryCountArray = preg_split("/UNION/", $sql);
        foreach ($queryCountArray as $q) {
            
            $q = preg_replace("/\s+ORDER\s+BY\s+.*DESC/U", "", $q);
            $q = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT regdate FROM ', $q, 1);
            $queries[] = preg_replace("/\s+ORDER\s+BY\s+.*DESC/U", "", $q);
            
        }
        $query = $queries[0] . " UNION " . $queries[1];
        */
    } else {
        $queryCount = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT COUNT(*) FROM ', $sql, 1);
        list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount);
        list($queryCount, ) = preg_split('/\bLIMIT\b/is', $queryCount);
    }
    return trim($queryCount);
    
}

/**
 * @param object PEAR::MDB2 instance
 * @param string db query
 * @param array  PEAR::Pager options
 * @param boolean Disable pagination (get all results)
 * @param integer fetch mode constant
 * @return array with links and paged data
 */
function Pager_Wrapper_MDB2(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB2_FETCHMODE_ASSOC, $params = array()) {
    if (!array_key_exists('totalItems', $pager_options)) {
        if ($countQuery = rewriteCountQuery($query)) {
            if (is_array($params) && $params[0] == '') $params = array();
            $totalItems = $db->extended->getOne($countQuery, NULL, $params);
            if (PEAR::isError($totalItems)) {
                return $totalItems;
            } else {
                $res =& $db->extended->getCol($query, NULL, $params);
                if (PEAR::isError($res)) return $res;
            }
            $totalItems = count($res);
        } else {
            $res =& $db->query($query, $dbparams);
            if (PEAR::isError($res)) return $res;
            $totalItems = (int)$res->numRows();
            $res->free();
        }
        $pager_options['totalItems'] = $totalItems;
    }
    
    require_once 'Pager/Pager.php';
    $pager = Pager::factory($pager_options);
    
    $page = array();
    $page['links'] = $pager->links;
    $page['totalItems'] = $pager_options['totalItems'];
    $page['page_numbers'] = array(
        'current' => $pager->getCurrentPageID(),
        'total'   => $pager->numPages()
    );
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
    $page['limit'] = $page['to'] - $page['from'] +1;
    if (!$disabled) {
        $db->setLimit($pager_options['perPage'], $page['from']-1);
    }
    $page['data'] = $db->extended->getAll($query, null, $params, NULL, $fetchMode);
    
    if (PEAR::isError($page['data'])) {
        return $page['data'];
    }
    if ($disabled) {
        $page['links'] = '';
        $page['page_numbers'] = array(
            'current' => 1,
            'total'   => 1
        );
    }
    return $page;
}
?>